package dbutils;

import bean.Customer;
import connectionPool.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Test;

import java.sql.*;

import java.util.List;
import java.util.Map;

/*
 * commons-dbutils 是Apache 组织提供的一个开源JDBC工具类，封装了针对数据库的增删查改操作
 * */
public class QueryRunnerTest {

    //测试插入
    @Test
    public void testInsert() {
        Connection connection = null;
        try {
            QueryRunner runner = new QueryRunner();
            connection = JDBCUtils.getConnection3();
            String sql = "insert into customers(name,email,birth) values(?,?,?)";
            int gao = runner.update(connection, sql, "高手", "gao@gmail.com", "1999-08-07");
            if (gao > 0) {
                System.out.println("插入成功");
            } else {
                System.out.println("插入失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            util.JDBCUtils.closeResourceConnection(connection, null);
        }
    }


    //查询操作（返回一条记录）BeanHandler
    @Test
    public void testQuery() {
        Connection connection = null;
        try {
            QueryRunner runner = new QueryRunner();
            connection = JDBCUtils.getConnection3();
            String sql = "select id,name,email,birth from customers where id=?";
            /*
             * BeanHandler: 是ResultSetHandler接口的实现类，用于封装表中的一条数据
             * */
            BeanHandler<Customer> handler = new BeanHandler<>(Customer.class);
            Customer customer = runner.query(connection, sql, handler, "2");
            System.out.println(customer.toString());
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            util.JDBCUtils.closeResourceConnection(connection, null);
        }
    }

    //查询操作（返回多条记录）BeanListHandler
    @Test
    public void testQuery2() {
        Connection connection = null;
        try {
            QueryRunner runner = new QueryRunner();
            connection = JDBCUtils.getConnection3();
            String sql = "select id,name,email,birth from customers where id<?";
            /*
             * BeanListHandler: 是ResultSetHandler接口的实现类，用于封装表中的多条数据构成的集合
             * */
            BeanListHandler<Customer> handlers = new BeanListHandler<>(Customer.class);
            List<Customer> customers = runner.query(connection, sql, handlers, "8");
            customers.forEach(System.out::println);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            util.JDBCUtils.closeResourceConnection(connection, null);
        }
    }

    //查询操作（返回一条键值对的记录）MapHandler
    @Test
    public void testQuery3() {
        Connection connection = null;
        try {
            QueryRunner runner = new QueryRunner();
            connection = JDBCUtils.getConnection3();
            String sql = "select id,name,email,birth from customers where id=?";
            /*
             * MapHandler: 是ResultSetHandler接口的实现类，对应表中的一条数据
             * 将字段及相应字段的值作为map中的key和value
             * */
            MapHandler handler = new MapHandler();
            Map<String, Object> map = runner.query(connection, sql, handler, "2");
            System.out.println(map);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            util.JDBCUtils.closeResourceConnection(connection, null);
        }
    }

    //查询操作（返回多条键值对的记录）MapListHandler
    @Test
    public void testQuery4() {
        Connection connection = null;
        try {
            QueryRunner runner = new QueryRunner();
            connection = JDBCUtils.getConnection3();
            String sql = "select id,name,email,birth from customers where id<?";
            /*
             * MapListHandler: 是ResultSetHandler接口的实现类，对应表中的多条记录
             * 将字段及相应字段的值作为map中的key和value，将这些map添加到List中
             * */
            MapListHandler mapListHandler = new MapListHandler();
            List<Map<String, Object>> mapList = runner.query(connection, sql, mapListHandler, "8");
            for (Map<String, Object> stringObjectMap : mapList) {
                System.out.println(stringObjectMap);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            util.JDBCUtils.closeResourceConnection(connection, null);
        }
    }

    /*
    * ScalarHandler: 用于查询特殊值
    * */
    //查询表中有多少记录 ScalarHandler
    @Test
    public void testQuery5() {
        Connection connection = null;
        try {
            QueryRunner runner = new QueryRunner();
            connection = JDBCUtils.getConnection3();
            String sql = "select count(*) from customers";

            ScalarHandler scalarHandler = new ScalarHandler();
            Long count = (Long) runner.query(connection, sql, scalarHandler);
            System.out.println("表中有" + count + "条记录");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            util.JDBCUtils.closeResourceConnection(connection, null);
        }
    }

    //查询表中最大出生日期 ScalarHandler
    @Test
    public void testQuery6() {
        Connection connection = null;
        try {
            QueryRunner runner = new QueryRunner();
            connection = JDBCUtils.getConnection3();
            String sql = "select max(birth) from customers";

            ScalarHandler scalarHandler = new ScalarHandler();
            Date maxBirth = (Date) runner.query(connection, sql, scalarHandler);
            System.out.println("表中最大的出生日期为: " + maxBirth);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            util.JDBCUtils.closeResourceConnection(connection, null);
        }
    }


    /*
    * 自定义ResultSetHandler的实现类
    * */
    @Test
    public void testQuery7() {
        Connection connection = null;
        try {
            QueryRunner runner = new QueryRunner();
            connection = JDBCUtils.getConnection3();
            String sql = "select id,name,email,birth from customers where id = ?";
            ResultSetHandler<Customer> handler = new ResultSetHandler<Customer>() {
                @Override
                public Customer handle(ResultSet rs) throws SQLException {
                    if (rs.next()) {
                        int id = rs.getInt("id");
                        String name = rs.getString("name");
                        String email = rs.getString("email");
                        Date birth = rs.getDate("birth");
                        Customer customer = new Customer(id, name, email, birth);
                        return customer;
                    }
                    return null;
                }
            };
            Customer customer = runner.query(connection, sql, handler, 5);
            System.out.println(customer.toString());
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            util.JDBCUtils.closeResourceConnection(connection, null);
        }
    }
}
